﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Text;
using System.Web.Script.Serialization;
using Amkeni_Monitoring_Plan.Classes;
using AmkeniCrossLoginApplication.Data_Access;

namespace Amkeni_Monitoring_Plan
{
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service1" in code, svc and config file together.
    // NOTE: In order to launch WCF Test Client for testing this service, please select Service1.svc or Service1.svc.cs at the Solution Explorer and start debugging.
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class Service1 : IService1
    {
        public FilterValueSet[] GetDistinctValue(string strColumnName, string strAlreadySelectedValue)
        {
            Amkeni_DatabaseEntities db = new Amkeni_DatabaseEntities();

            SqlConnection connection = null;
            //EntityConnection conn = null;

            try
            {
                connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

                //IQueryable data = null;
                string strSQL = string.Empty;
                switch (strColumnName) // This is the (defined) column name in the GridView
                {
                    /*case "ip_id":
                        strSQL = @"SELECT DISTINCT monitoring_plan.ip_id, implementing_partner.abbreviation FROM implementing_partner JOIN monitoring_plan ON implementing_partner.ip_id = monitoring_plan.ip_id";
                        //data = (from d in db.programmes select d.name).Distinct();
                        break;*/
                    case "Call":
                        strSQL = @"SELECT DISTINCT Call, Call FROM monitoring_plan_view";
                        break;
                    case "abbreviation":
                        strSQL = @"SELECT DISTINCT abbreviation, abbreviation FROM monitoring_plan_view";
                        //data = (from d in db.programmes select d.name).Distinct();
                        break;
                    case "outcome_id":
                        strSQL = @"SELECT DISTINCT outcome_id, outcome_id FROM monitoring_plan_view";
                        break;
                    case "output_id":
                        strSQL = @"SELECT DISTINCT output_id, output_id FROM monitoring_plan_view";
                        //data = (from d in db.projects select d.name).Distinct();
                        break;
                    case "output_indicator_id":
                        strSQL = @"SELECT DISTINCT output_indicator_id, output_indicator_id FROM monitoring_plan_view";
                        //data = (from d in db.regions select d.name).Distinct();
                        break;
                    case "year":
                        strSQL = @"SELECT DISTINCT year, year FROM monitoring_plan_view";
                        break;
                    case "indicator_id, output_indicator_id":
                        strSQL = @"SELECT DISTINCT indicator_id, output_indicator_id FROM monitoring_plan_view";
                        break;
                    /*case "year_id":
                        strSQL = @"SELECT DISTINCT monitoring_plan.year_id, year.year FROM year JOIN monitoring_plan ON year.year_id = monitoring_plan.year_id";
                        break;*/
                }

                SelectedDataCollection objSelectedDataCollection = null;

                if (strAlreadySelectedValue.Trim().Length > 0)
                {
                    var objJavaScriptSerializer = new JavaScriptSerializer();
                    objSelectedDataCollection = new SelectedDataCollection();
                    objSelectedDataCollection = objJavaScriptSerializer.Deserialize<SelectedDataCollection>(strAlreadySelectedValue.Trim());

                    foreach (SelectedData selectedData in objSelectedDataCollection.DataCollection)
                    {
                        if (selectedData.ColumnName != strColumnName)
                        {
                            if (selectedData.SelectedValue.Count<string>() > 0)
                            {
                                strSQL = strSQL + ((strSQL.Contains(" WHERE ") == true) ? " AND " : " WHERE ") + selectedData.ColumnName + " IN " + ArrayToString(selectedData.SelectedValue);
                            }
                        }
                    }
                }

                SqlCommand command = new SqlCommand();
                command.CommandText = strSQL;
                command.Connection = connection;

                command.Connection.Open();

                SqlDataReader dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);

                IList<FilterValueSet> filterValueList = new List<FilterValueSet>();

                while (dataReader.Read())
                {
                    filterValueList.Add(new FilterValueSet
                    {
                        Id = dataReader[0].ToString().Trim(),
                        Value = dataReader[1].ToString().Trim()
                    });
                }
                connection.Close();

                return filterValueList.ToArray<FilterValueSet>();
            }
            catch (Exception ex)
            {
                //if (conn != null) conn.Close();
                if (connection != null) connection.Close();
                throw new Exception("Error in the Service " + ex.Message);
            }
        }

        private string ArrayToString(IEnumerable<string> strValues)
        {
            string strValue = string.Empty;
            foreach (string strVal in strValues)
            {
                strValue = strValue + "'" + strVal + "', ";
            }
            if (strValue.Length > 0)
            {
                strValue = "(" + strValue.Substring(0, strValue.Length - 2) + ")";
            }
            return strValue;
        }
    }

    public class FilterValueSet
    {
        public string Id { get; set; }
        public string Value { get; set; }
    }
}
